package server;

import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Vector;

import client.model.Track;
//import com.sun.deploy.Environment;
//import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbutils.QueryLoader;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.sound.sampled.AudioFormat;
import javax.sound.sampled.AudioInputStream;
import javax.sound.sampled.AudioSystem;
import javax.sound.sampled.UnsupportedAudioFileException;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import java.io.*;
import java.lang.ref.WeakReference;
import java.sql.*;
import java.sql.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipOutputStream;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
//import org.apache.log4j.Logger;
//import org.apache.log4j.PropertyConfigurator;
import javax.mail.*;
import javax.mail.internet.*;
import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;
//import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
//import org.apache.commons.dbcp.BasicDataSource;

/**
 * Created by swootton on 27/02/14.
 */

public class MFHttpServlet extends HttpServlet {

    // does not hold any context variables,
    // purely utility class to avoid redundant code.
    // not used to share data

    public MFHttpServlet() {

    }

    @Override
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        System.out.println("MFHttpServlet.doGet()"+"\n");
    }

    public void write(HttpServletResponse response, StringBuilder strResp){

        System.out.println(strResp);

        // response.setContentType("text/html; charset=UTF-8");
        // Required to print XML in browser window not just view source
        response.setContentType("text/xml");
        response.setHeader("Content-type","application/xhtml+xml");
        //response.setHeader("MFServer-Error", "true");

        PrintWriter out = null;

        try {
            out = response.getWriter();
            System.out.println(strResp.toString());
            out.println(strResp.toString());
        } catch (IOException e) {
            System.out.println(e.toString());
            e.printStackTrace();
        }
    }

    protected void writeErrorResponse(HttpServletRequest request, HttpServletResponse response, String errorCode){

        StringBuilder strResp = new StringBuilder();
        strResp.append("<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\n");
        strResp.append("<MFResponse>");
        strResp.append("<Response type='Error' code='"+errorCode+"'>");
        strResp.append("</Response>");
        strResp.append("</MFResponse>");

        response.setHeader("MFServer-Error", "true");

        write(response, strResp);
    }

    protected StringBuilder getGenres(int userRepository) {

        System.out.println("MFHttpServlet.getGenres() userRepository "+userRepository);

        /**
         * Excludes genres with 0 tracks:
         *
         * select mf_genres.name, mf_genres.description, mf_genres.updated, count(*) as num_tracks from mf_genres, mf_tracks where mf_tracks.genre = mf_genres.id group by mf_genres.name, mf_genres.description, mf_genres.updated;
         */

        /*String GENRE_QUERY = "SELECT mf_genres.name, mf_genres.description, mf_genres.updated, " +
                "COUNT(*) AS num_tracks " +
                "FROM mf_genres, mf_tracks " +
                "WHERE mf_tracks.genre = mf_genres.id AND mf_genres.repository='"+userRepository+"' " +
                "group by mf_genres.name, mf_genres.description;";  //, mf_genres.updated  */

        /*String GENRE_QUERY = "SELECT mfg.name as catagory,\n" +
                    "COUNT(mf_tracks.name) AS num_songs,\n" +
                    "mfg.updated AS last_update,\n" +
                    "mfg.description AS description\n" +
                    "FROM mf_tracks, musicfinder.mf_genres mfg \n" +
                    //"WHERE mf_tracks.genre = mfg.id\n" +
                    "GROUP BY mfg.id;";*/

        StringBuilder strResp = new StringBuilder();

        //System.out.println(GENRE_QUERY);

        Connection connection = (Connection) getServletContext().getAttribute("db_connection");
        Properties props = (Properties) getServletContext().getAttribute("properties");

        try {
            Class.forName("com.mysql.jdbc.Driver");  //registering the jdbc driver here

            //db_con = DriverManager.getConnection(db_url, db_user, db_pwd);
            //db_st = db_con.createStatement();
            // TODO MAJOR HACK should be music-alms.sql.getGenres
            // TODO is now music-alms.sql.getGenres, cannot remember why was _tmp version.
            // TODO do not want to exclude genres with 0 tracks as user might be first to add to genre
            PreparedStatement ps = connection.prepareStatement(props.getProperty("music-alms.sql.getGenres"));

            ps.setInt(1, userRepository);

            ResultSet db_rs = ps.executeQuery();

            //strResp.append("<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\n");
            //strResp.append("<MFResponse>\n");
            //strResp.append("<Response type='Catagories'>\n");

            while (db_rs.next()) {

                String genre = db_rs.getString(1);
                String description = db_rs.getString(2);
                Timestamp updated = db_rs.getTimestamp(3);
                String numTracks = db_rs.getString(4);

                String str_updated = new SimpleDateFormat("MM/dd/yyyy").format(updated);

                strResp.append("<Genre name='"+genre+"' num_entries='"+numTracks+"' updated='"+str_updated+"' description='"+description+"'/>"+"\n");
            }

            //strResp.append("</Response>\n");
            //strResp.append("</MFResponse>\n");

        }
        catch(ClassNotFoundException e1) {

            System.out.println(e1.getMessage());
            e1.printStackTrace();
        }
        catch (SQLException e2) {

            System.out.println(e2.getMessage());
            e2.printStackTrace();
        }

        return strResp;
    }

    protected StringBuilder getUserSettings(int userID) {

        System.out.println("getUserSettings()");

        // TODO dont allow duplicate usernames as its so important to session, also wouldnt want it in software/GUI anyway
        // but work off user ID instead just to be safe

        //int userID = getUser(username); // from session in calling method

        StringBuilder query = new StringBuilder();

        String GET_USER_SETTINGS = "SELECT download_dir, email_notify, auto_open, clear_uploads, decompress FROM mf_users_settings WHERE user='" + userID+"';";

        String download_dir = "";
        String email_notify = "";
        String auto_open = "";
        String clear_uploads = "";
        String decompress = "";

        Connection connection = (Connection) getServletContext().getAttribute("db_connection");

        try {
            //Class.forName("com.mysql.jdbc.Driver");  //registering the jdbc driver here

            //db_con = DriverManager.getConnection(db_url, db_user, db_pwd);
            Statement db_st = connection.createStatement();
            ResultSet db_rs = db_st.executeQuery(GET_USER_SETTINGS);

            while (db_rs.next()) {
                download_dir = db_rs.getString(1);
                email_notify = db_rs.getString(2);
                auto_open = db_rs.getString(3);
                clear_uploads = db_rs.getString(4);
                decompress = db_rs.getString(5);
            }
        }
        /*catch(ClassNotFoundException e1) {

            System.out.println(e1.getMessage());
            e1.printStackTrace();
        }*/
        catch (SQLException e2) {

            System.out.println(e2.getMessage());
            e2.printStackTrace();
        }

        StringBuilder strResp = new StringBuilder("<UserSettings download_dir='"+download_dir+
                "' email_notify='"+email_notify+
                "' auto_open='"+auto_open+
                "' clear_uploads='"+clear_uploads+
                "' decompress='"+decompress+"'/>");

        return strResp;
    }

    protected void notifyUsersOfActivity(String emailMsg, HttpSession serverSession) {

        System.out.println("notifyUsersOfUpload()");

        ServletContext servletContext = serverSession.getServletContext();

        Properties props = (Properties) servletContext.getAttribute("properties");

        String emailUser = props.getProperty("music-alms_admin");
        String emailPass = props.getProperty("music-alms_pass");
        String emailHost = props.getProperty("music-alms_host");


        // http://www.tutorialspoint.com/java/java_sending_email.htm
        //ServletContext.class.getResource("server.properties");


        Vector<String> emailAddresses = getEmailAddresses(serverSession);

        // Get system properties
        Properties properties = System.getProperties();

        // Get the default Session object.
        Session session = Session.getDefaultInstance(properties);

        PasswordAuthentication authenticator = new javax.mail.PasswordAuthentication(emailUser,emailPass);

        // Setup mail server
        properties.setProperty("mail.smtp.host", emailHost);
        properties.put("mail.smtp.port", "587");
        properties.setProperty("mail.smtp.auth", "true");
        properties.put("mail.smtp.starttls.enable", "true");
        //properties.setProperty("mail.smtp.submitter", authenticator.getUserName());
        props.put("mail.smtp.user", emailUser);
        properties.put("mail.smtp.password", emailPass);

        session.setDebug(true);   // required for output to catalina.out

        try {

            // Create a default MimeMessage object.
            MimeMessage message = new MimeMessage(session);

            // info@music-alms.com

            message.setFrom(new InternetAddress("sam.wootton@gmail.com"));

            for(int i = 0; i < emailAddresses.size(); i++) {
                String tmpEmail = emailAddresses.get(i);
                System.out.println("Notifying "+tmpEmail);
                message.addRecipient(Message.RecipientType.TO, new InternetAddress(tmpEmail));
            }


            message.setSubject("Music-alms Activity");

            message.setText(emailMsg);


            Transport transport = session.getTransport("smtp");
            transport.connect(emailHost, emailUser, emailPass);
            transport.sendMessage(message, message.getAllRecipients());
            transport.close();

            System.out.println("Sent message successfully....");

        } catch (MessagingException mex) {
            mex.printStackTrace();
        }
    }

    protected Vector<String> getEmailAddresses(HttpSession serverSession) {
        // Get users of same repository and who want to recieve email notifications

        /**
         * SELECT mf_users.id, mf_users.email_address
         * FROM mf_users
         * JOIN mf_users_settings
         * ON mf_users_settings.user = mf_users.id
         * WHERE mf_users_settings.email_notify='yes' AND mf_users.repository=1
         * ORDER BY mf_users.email_address
         */
        int repositoryID = Integer.parseInt(serverSession.getAttribute("userRepository").toString());

        String EMAIL_LIST_QUERY = "SELECT mf_users.email_address " +
                "FROM mf_users " +
                "JOIN mf_users_settings " +
                "ON mf_users_settings.user = mf_users.id " +
                "WHERE mf_users_settings.email_notify='yes' " +
                "AND mf_users.repository="+repositoryID +
                " ORDER BY mf_users.email_address;";

        System.out.println("Getting all users for repositoryID = "+repositoryID);
        System.out.println("EMAIL_LIST_QUERY "+EMAIL_LIST_QUERY);

        Vector<String> emailAddresses = new Vector<String>();

        try {
            //Class.forName("com.mysql.jdbc.Driver");  //registering the jdbc driver here

            //db_con = DriverManager.getConnection(db_url, db_user, db_pwd);
            Connection connection = (Connection) getServletContext().getAttribute("db_connection");
            Statement db_st = connection.createStatement();
            ResultSet db_rs = db_st.executeQuery(EMAIL_LIST_QUERY);

            while (db_rs.next()) {
                emailAddresses.add(db_rs.getString(1));
            }
        }
        catch (SQLException e2) {

            System.out.println(e2.getMessage());
            e2.printStackTrace();
        }

        return emailAddresses;
    }
}
